package com.smarttest.portal.service.impl;

import java.util.LinkedHashMap;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.smarttest.portal.mapper.DbAutoReleaseMapper;
import com.smarttest.portal.pojo.DbAutoRelease;
import com.smarttest.portal.pojo.DbAutoReleaseExample;
import com.smarttest.portal.pojo.DbAutoReleaseExample.Criteria;
import com.smarttest.portal.service.AutoG7ReportService;
@Service
public class AutoG7ReportServiceImpl implements AutoG7ReportService {

	@Autowired
	DbAutoReleaseMapper DbAutoReleaseMapper;
	@Override
	public List<LinkedHashMap<String, Object>> GetReports(int env,int releaseid, List<Integer> modules, List<Integer> ids, List<Integer> importants,
			List<String> owners, List<Integer> levellist, List<String> results, String time) {
		// TODO Auto-generated method stub
		String sql = "";
		String []timearr = time.replaceAll("\"", "").split(" - ");
		//除了测试环境，什么都不选
		if (releaseid == 0 && modules == null && ids == null && importants == null && owners == null 
				&& levellist == null && results == null) {
			sql = "SELECT * from db_auto_release t1 join (SELECT releaseid,count(case when result='PASS' then result else null end) as pass,count(case when result='FAIL' then result else null end) as fail,count(case when result='SKIP' then result else null end) as skip, SUM(usetimemsec) as 'usetimesec',"+
					"exctime from db_auto_summary_excresult GROUP BY  releaseid) t2 on t1.id = t2.releaseid WHERE t1.envid = "+env+" and exctime >= \""+timearr[0]+"\" and exctime < \""+timearr[1]+"\" order by exctime;";
			return DbAutoReleaseMapper.superManagerSelect(sql);
		}	
		//选择版本和单接口
		else if (releaseid != 0 && ids != null && ids.size() == 1) {
			String sql1 = "";
			if (levellist != null) {
				String levelstr = "";
				for (int level : levellist) {
					if (!levelstr.isEmpty()) {
						levelstr += ",";
					}
					levelstr += level;
				}
				sql1 = " and level in("+levelstr+")";
			}
			sql = "SELECT * FROM db_auto_details_excresult t1 join db_auto_interfacescenedetails t2 on t1.detailid = t2.id WHERE sumresultid = (SELECT id from db_auto_summary_excresult WHERE sumid = "+ids.get(0)+" and releaseid = "+releaseid+" and exctime >= \""+timearr[0]+"\" and exctime < \""+timearr[1]+"\") "+sql1 +" ORDER BY t1.detailid";
			return DbAutoReleaseMapper.superManagerSelect(sql);
		}
		//选择测试结果或者不选版本、选单接口
		else if (results != null  ||(releaseid == 0 && ids != null && ids.size() == 1)){
			String sql2 = " where exctime >= \""+timearr[0]+"\" and exctime < \""+timearr[1]+"\" and tt.envid = "+ env ;
			if (modules != null) {
				String moduleids = "";
				for (int moduleid : modules) {
					if (!moduleids.isEmpty()) {
						moduleids += ",";
					}
					moduleids += moduleid;
				}
				sql2 += " and tt.moduleid in (" + moduleids + ")";
			}
			if (ids != null) {
				String idstr = "";
				for (int id : ids) {
					if (!idstr.isEmpty()) {
						idstr += ",";
					}
					idstr += id;
				}
				sql2 += " and tt.id in (" + idstr + ")";
			}
			if (importants != null) {
				String importantstr = "";
				for (int importantid : importants) {
					if (!importantstr.isEmpty()) {
						importantstr += ",";
					}
					importantstr += importantid;
				}
				sql2 += " and tt.degreeid in (" + importantstr + ")";
			}
			if (owners != null) {
				String ownerstr = "";
				for (String owner : owners) {
					if (!ownerstr.isEmpty()) {
						ownerstr += ",";
					}
					ownerstr += "\""+owner+"\"";
				}
				sql2 += " and tt.tester in (" + ownerstr + ")";
			}
			if (results != null) {
				String resultsstr = "";
				for (String result : results) {
					if (!resultsstr.isEmpty()) {
						resultsstr += ",";
					}
					resultsstr += "\""+result+"\"";
				}
				sql2 += " and t.result in (" + resultsstr + ")";
			}
			sql = "SELECT t.*,tt.* from db_auto_summary_excresult t JOIN (SELECT t2.*,t3.`name` as env,t4.`name` as module,t5.degree from db_auto_interfacesummary t2 JOIN db_auto_environment t3 on t2.envid = t3.id JOIN db_auto_module t4 "+
					"on t2.moduleid = t4.id JOIN db_auto_degree t5 on t2.degreeid = t5.id) tt on t.sumid = tt.id"+sql2;
//			join db_auto_release rt on t.releaseid = rt.id
			return DbAutoReleaseMapper.superManagerSelect(sql);
		}
		//其他情况
		else {
			String sql1 = "SELECT t.sumid,count(case when t.result='PASS' then t.result else null end) as pass,count(case when t.result='FAIL' then t.result else null end) as fail,count(case when t.result='SKIP' then t.result else null end) as skip from (SELECT * from db_auto_summary_excresult WHERE exctime >= \""+timearr[0]+"\" and exctime < \""+timearr[1]+"\"";
			if (releaseid != 0) {
				sql1+=" and releaseid  = "+releaseid;
			}
			String sql2 = " where t2.envid = "+ env ;
			if (modules != null) {
				String moduleids = "";
				for (int moduleid : modules) {
					if (!moduleids.isEmpty()) {
						moduleids += ",";
					}
					moduleids += moduleid;
				}
				sql2 += " and t2.moduleid in (" + moduleids + ")";
			}
			if (ids != null) {
				String idstr = "";
				for (int id : ids) {
					if (!idstr.isEmpty()) {
						idstr += ",";
					}
					idstr += id;
				}
				sql2 += " and t2.id in (" + idstr + ")";
			}
			if (importants != null) {
				String importantstr = "";
				for (int importantid : importants) {
					if (!importantstr.isEmpty()) {
						importantstr += ",";
					}
					importantstr += importantid;
				}
				sql2 += " and t2.degreeid in (" + importantstr + ")";
			}
			if (owners != null) {
				String ownerstr = "";
				for (String owner : owners) {
					if (!ownerstr.isEmpty()) {
						ownerstr += ",";
					}
					ownerstr += "\""+owner+"\"";
				}
				sql2 += " and t2.tester in (" + ownerstr + ")";
			}
			sql = "SELECT t2.*,t3.`name` as env,t4.`name` as module,t5.degree,t1.* from db_auto_interfacesummary t2 JOIN db_auto_environment t3 on t2.envid = t3.id JOIN db_auto_module t4 "+
					"on t2.moduleid = t4.id JOIN db_auto_degree t5 on t2.degreeid = t5.id "+
					"JOIN ("+sql1+")t GROUP BY  t.sumid) t1 on t2.id = t1.sumid"
							+ sql2;
			return DbAutoReleaseMapper.superManagerSelect(sql);
		}
	}
	@Override
	public List<DbAutoRelease> getReleases(int envid) {
		DbAutoReleaseExample example = new DbAutoReleaseExample();
		Criteria criteria = example.createCriteria();
		criteria.andEnvidEqualTo(envid);
		List<DbAutoRelease> list = DbAutoReleaseMapper.selectByExampleWithBLOBs(example);
		return list;
	}
}
